using System;
using System.Configuration;
using System.Web.Configuration;
using System.Security.Cryptography;
using MOM.DB.Access;
using Mono.Data.Sqlite;

namespace MOM
{
	public partial class Upgrade : System.Web.UI.Page
	{
		protected void Page_Load(object sender, EventArgs e)
		{			
			string version = "1.0";
			if (ConfigurationManager.AppSettings["CurrentVersion"] != null)
				version = ConfigurationManager.AppSettings["CurrentVersion"];
			Response.Write("<html><body>Found version " + version + " of M.O.M<br/>");

			double versionNo = 1.0;
            if (version.StartsWith("1.0.5"))
                versionNo = 1.05;
            else if (version.StartsWith("1.1.0"))
                versionNo = 1.1;
            else if (version.StartsWith("1.1.1"))
                versionNo = 1.11;

            Configuration webCfg = WebConfigurationManager.OpenWebConfiguration("~/");

			using (SqliteServer conn = new SqliteServer("Default", true))
			{

				if (versionNo < 1.05)
				{
					using (SqliteDataReader reader = conn.GetDataReader("select * from sqlite_master where type = 'table' and name = 'tblHistory'"))
					{
						if (!reader.Read())
						{
							conn.Execute(@"CREATE TABLE tblHistory(
								UserId INTEGER,
								TrackId INTEGER,
								DatePlayed DATETIME DEFAULT CURRENT_TIMESTAMP
							)");
						}
					}
				}

				if (versionNo < 1.1)
				{
					//Save Encryption keys to web.config
					Rijndael alg = Rijndael.Create();
					alg.GenerateKey();
					alg.GenerateIV();
					Configuration webConfig = WebConfigurationManager.OpenWebConfiguration("~/");
					webConfig.AppSettings.Settings.Add("RijndaelKey", Convert.ToBase64String(alg.Key));
					webConfig.AppSettings.Settings.Add("RijndaelIV", Convert.ToBase64String(alg.IV));
					webConfig.AppSettings.Settings.Add("CurrentVersion", "1.1.0");
					webConfig.AppSettings.Settings.Add("EnableShareSwitching", "true");
					webConfig.AppSettings.Settings.Add("SynchCheckFiles", "false");
					webConfig.AppSettings.Settings.Add("SharedRemoveTrack", "Played");
					webConfig.AppSettings.Settings.Add("SharedRemoveTrackTime", "60");
					webConfig.AppSettings.Settings.Add("OutputCache", "true");
					webConfig.AppSettings.Settings.Add("OutputCacheTime", "12");

					try
					{
						webConfig.Save();
					}
					catch
					{
						Response.Write("Unable to write to web.config. Please make sure the local IISWPG account (win 2003) or ASPNET account (other) has writing access to it.");
						Response.End();
					}

					//Update Track Trigger For Delete
					try
					{
						conn.Execute("DROP TRIGGER DurationDelete");
					}
					catch { }

					conn.Execute(@"CREATE TRIGGER DurationDelete AFTER DELETE ON tblTrack
					BEGIN
						UPDATE tblAlbum SET AlbumDuration = (AlbumDuration - old.TrackDuration) WHERE AlbumId = old.AlbumId;
						UPDATE tblArtist SET ArtistDuration = (ArtistDuration - old.TrackDuration) WHERE ArtistId = old.ArtistId;
						DELETE FROM tblHistory WHERE TrackId = old.TrackId;					
						DELETE FROM tblAlbum WHERE tblAlbum.AlbumId = old.AlbumId AND AlbumId NOT IN (SELECT AlbumId FROM tblTrack WHERE tblTrack.AlbumId = old.AlbumId);					
						DELETE FROM tblArtist WHERE tblArtist.ArtistId = old.ArtistId AND ArtistId NOT IN (SELECT ArtistId FROM tblTrack WHERE tblTrack.ArtistId = old.ArtistId);
					END");

					//tblPlaylistShared
					conn.Execute(@"CREATE TABLE tblPlaylistShared(
					UserName TEXT,
					TrackId INTEGER,
					TrackName TEXT,
					AlbumId INTEGER,
					ArtistId INTEGER,
					ArtistName TEXT,
					DateAdded DATETIME DEFAULT CURRENT_TIMESTAMP
					)");

					//tblBookmark
					conn.Execute(@"CREATE TABLE tblBookmark(
					BookmarkId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
					UserId INTEGER NOT NULL,
					BookmarkUrl TEXT COLLATE NOCASE NOT NULL,
					BookmarkName TEXT NOT NULL,
					BookmarkCategory INTEGER NOT NULL DEFAULT((1)),
					BookmarkIsRss INTEGER NOT NULL DEFAULT((0)),
					BookmarkIsRssDefault INTEGER NOT NULL DEFAULT((0))
					)");

					//Convert Sys To Shares
					conn.Execute(@"CREATE TABLE tblShares(
					ShareId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
					ShareParentId INTEGER NOT NULL DEFAULT((0)),
					ShareName TEXT COLLATE NOCASE NOT NULL,
					ShareDirectory TEXT NOT NULL,
					SharePriority INTEGER NOT NULL DEFAULT((10)),
					ShareEnabled INTEGER NOT NULL DEFAULT((1)),
					DateSynched DATETIME
					)");

					conn.Execute("ALTER TABLE tblTrack ADD COLUMN ShareId INTEGER NOT NULL DEFAULT((1))");

					string path = "";
					DateTime synched = DateTime.MinValue;
					int shareId = 1;
					using (SqliteDataReader reader = conn.GetDataReader("SELECT * FROM tblSys"))
					{
						if (reader.Read())
						{
							path = (string)reader["FilePath"];
							if (reader["LastSyncDate"] != DBNull.Value)
								synched = Convert.ToDateTime(reader["LastSyncDate"]);
						}
					}
					if (path != "")
					{
						shareId = conn.Execute("INSERT INTO tblShares(ShareName, ShareDirectory, DateSynched) VALUES('{0}', '{1}', '{2}');SELECT last_insert_rowid()", "Default", path, synched.ToString("yyyy-MM-dd HH:mm:ss"));
					}

					conn.Execute("UPDATE tblTrack SET ShareId = {0}", shareId);

					conn.Execute("DROP TABLE tblSys");

					//Add Some Columns
					conn.Execute(@"ALTER TABLE tblArtist ADD COLUMN ArtistWiki TEXT;
								ALTER TABLE tblArtist ADD COLUMN IsActive INTEGER NOT NULL DEFAULT ((1));
								ALTER TABLE tblAlbum ADD COLUMN IsActive INTEGER NOT NULL DEFAULT ((1));
								ALTER TABLE tblTrack ADD COLUMN TrackDisabled INTEGER NOT NULL DEFAULT((0));
								ALTER TABLE tblUser ADD COLUMN UserPasswordCrypt TEXT NOT NULL DEFAULT('');
								ALTER TABLE tblUser ADD COLUMN UserSalt TEXT NOT NULL DEFAULT('');
								ALTER TABLE tblUser ADD COLUMN Theme TEXT NOT NULL DEFAULT('green');
								ALTER TABLE tblUser ADD COLUMN LastFmPasswordCrypt TEXT;
					");

					//Create Indexes
					conn.Execute(@"CREATE UNIQUE INDEX [idxArtistName] ON [tblArtist] ([ArtistName] COLLATE NOCASE);
								CREATE INDEX [idxTrackName] ON [tblTrack] ([TrackName] COLLATE NOCASE);
								CREATE INDEX [idxTrackArtist] ON [tblTrack] ([TrackArtist] COLLATE NOCASE);
								CREATE INDEX [idxTrackFile] ON [tblTrack] ([TrackFile])");

					//Encrypt Passwords
					using (SqliteDataReader reader = conn.GetDataReader("SELECT UserId, UserName, UserPassword, LastFmPassword FROM tblUser"))
					{
						while (reader.Read())
						{
							string salt = "";
							string pwd = MOM.DB.MOMDB.GetSaltedPassword((string)reader["UserPassword"], null, out salt);
							string lastFmPwd = "";
							if (reader["LastFmPassword"] != DBNull.Value)
								lastFmPwd = MOM.DB.MOMDB.EncryptRijndael((string)reader["LastFmPassword"], alg.Key, alg.IV);
							conn.Execute("UPDATE tblUser SET UserPasswordCrypt = '{0}', UserSalt = '{1}', UserPassword = '', LastFmPasswordCrypt = '{2}', LastFmPassword = '' WHERE UserId = {3}", pwd, salt, lastFmPwd, reader["UserId"].ToString());
						}
					}
				}

                if (versionNo < 1.11)
                {
                    try
                    {
                        conn.Execute("ALTER TABLE tblTrack ADD COLUMN TrackLyric TEXT COLLATE NOCASE;");
                        conn.Execute("CREATE INDEX idxTrackLyric ON tblTrack (TrackLyric COLLATE NOCASE)");
                    }
                    catch (Exception x)
                    {
                        if (x.Message.IndexOf("duplicate") == -1 && x.Message.IndexOf("exists") == -1)
                            throw x;
                    }

                    if (webCfg.AppSettings.Settings["CacheLyrics"] == null)
                        webCfg.AppSettings.Settings.Add("CacheLyrics", "true");
                }
			}

            //Save New Version To Config
            webCfg.AppSettings.Settings["CurrentVersion"].Value = "1.1.1.0";
            try
            {
                webCfg.Save();
            }
            catch
            {
                Response.Write("Unable to write to web.config. Please make sure the local IISWPG account (win 2003) or ASPNET account (other) has writing access to it.");
                Response.End();
            }

			Response.Write("Upgrade complete</body></html>");
		}
	}
}
